package com.suineg.pms.repository.base;

import java.util.List;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;

import com.suineg.pms.entity.base.Room;

/**
 *
 *@author Suine
 */
public interface RoomDao extends PagingAndSortingRepository<Room, Long>,JpaSpecificationExecutor<Room>{
	
	Room findByHouseNo(String houseNo);
	
	@Query("from Room r where r.building.id =?1 and r.unit=?2 and r.roomNo=?3")
	List<Room> findByBuildingAndUnitAndRoomNo(Long buildingId,String unit,String roomNo);
	
	@Query("select max(r.unit) from Room r where r.building.id=?1")
	String findMaxUnitNoByBuidlingId(Long buildingId);
	
	@Query("from Room r where r.houseNo in (?1)")
	List<Room> findRoomByHouseNos(String houseNos);
	
	@Query(nativeQuery = true, value = "SELECT max(maxRoomNo) FROM ( SELECT CASE WHEN LENGTH(r.room_no) > 3 THEN SUBSTR(r.room_no, 1, 2) ELSE SUBSTR(r.room_no, 1, 1) END floor, SUBSTR(LPAD(max(r.room_no), 4, '0'), 3) maxRoomNo FROM pms_room r WHERE r.building_id = ?1 and r.unit=?2 GROUP BY CASE WHEN LENGTH(r.room_no) > 3 THEN SUBSTR(r.room_no, 1, 2) ELSE SUBSTR(r.room_no, 1, 1) END ) b")
	String findRooMaxByBuidingId(Long id,String unit);
	
}

